-- @owner: wan005
-- @date: 2024/10/8
-- @testpoint: pipelined函数返回object嵌套表类型，部分合理报错
-- @modify: modified by zhangbingliang at 2025.01,pipelined需求已支持
--pre1:切换A库执行
@conn PrimaryDbAmode;

--step1:创建object嵌套表类型;expect:成功
create or replace type type_0003 as object(c1 integer, c2 tinyint, c3 numeric(10, 4), c4 decimal(12),
c5 number(10,4), c6 float(10), c7 bit(1), c8 dec(10,3), c9 boolean, c10 char(10),
c11 nchar(10), c12 varchar(200), c13 varchar2(20), c14 text, c15 blob, c16 date, c17 time);
create or replace type tb_type_0003 as table of type_0003;

--step2:创建管道函数并查看返回值;expect:成功
create or replace function func_pipelined_0003(count in number)
returns tb_type_0003 pipelined
as $$
declare result type_0003;
begin
for i in 1 .. count loop
result.c1 = i + 1;
result.c2 = i + 1;
result.c3 = i + 0.005;
result.c4 = i - 0.5;
result.c5 = i ^ 2;
result.c6 = i * 0.5;
result.c7 = 1;
result.c8 = (i / 0.5);
result.c9 = true;
result.c10 = '1sadf1234';
result.c11 = 'sdfwdf';
result.c12 = '123wsdfwaerwefasd';
result.c13 = 'sdfwersdf';
result.c14 = 'exdgeww';
result.c15 = '1'::blob;
result.c16 = date'2022-01-01';
result.c17 = time'11:11:11';
pipe row(result);
end loop;
end;
$$ language plpgsql;
/

--step3:调用pipelined函数并查看返回类型;expect:成功
select func_pipelined_0003(3);
select * from func_pipelined_0003(3);
select * from table(func_pipelined_0003(3));
select c1 from table(func_pipelined_0003(3));
select pg_typeof(func_pipelined_0003(3));

--step4:使用alter修改函数名称;expect:成功
alter function func_pipelined_0003(count in number) rename to func_pipelined_0003_1;
select func_pipelined_0003_1(3);
select * from func_pipelined_0003_1(3);
select * from table(func_pipelined_0003_1(3));

--step5:使用create table as创建表;expect:失败
drop table if exists t_pipelined_0003;
create table t_pipelined_0003 as select * from func_pipelined_0003(1);
insert into t_pipelined_0003 (select * from table(func_pipelined_0003(1)));

--step6:清理环境;expect:成功
drop type if exists tb_type_0003 cascade;
drop type if exists type_0003 cascade;
drop function if exists func_pipelined_0003_1;
drop table if exists t_pipelined_0003;
